package com.scu.dao;


import com.scu.entity.Good;
import com.scu.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodDao {

    public static List<Good> findAll() throws SQLException {

        //1. 连接
        Connection conn = JDBCUtil.getConnection();

        //2. sql
        String sql = "select * from good";
        PreparedStatement prep = conn.prepareStatement(sql);

        //3. 结果
        ResultSet rs = prep.executeQuery();
        //处理结果
        List<Good> list = new ArrayList<Good>();
        while (rs.next()) {
            //rs代表的就是每一行
            Good good = new Good();

            int id = rs.getInt("id");
            String goodname = rs.getString("goodname");
            int year = rs.getInt("year");
            int month = rs.getInt("month");
            int day = rs.getInt("day");
            good.setId(id);
            good.setGoodname(goodname);
            good.setYear(year);
            good.setMonth(month);
            good.setDay(day);
            list.add(good);
        }

        //4. 关闭连接
        conn.close();

        return list;
    }
//    public static void main(String[] args) throws SQLException {
//        List<Good> list = new GoodDao().findAll();
//        System.out.println(list);
//    }
        //添加一个用户
     public static int insert(Good good) throws SQLException {

        //1. 连接
         Connection conn = JDBCUtil.getConnection();

         //2. sql
//         String sql = "INSERT INTO good(id,goodname,day,month,year,username) VALUES(?,?,?,?,?,?)";
//         PreparedStatement prep = conn.prepareStatement(sql);
//         prep.setInt(1, good.getId());
//         prep.setString(2, good.getGoodname());
//         prep.setInt(3, good.getDay());
//         prep.setInt(4, good.getMonth());
//         prep.setInt(5, good.getYear());
//         prep.setString(6, good.getUsername());
//         String sql = "INSERT INTO good(id,goodname,day,month,year,username) VALUES(?,?,?,?,?,?)";
//         PreparedStatement prep = conn.prepareStatement(sql);
//         prep.setInt(1, good.getId());
//         prep.setString(2, good.getGoodname());
//         prep.setInt(3, 15);
//         prep.setInt(4,7);
//         prep.setInt(5, 2022);
//         prep.setString(6, "许凯禹");
         String sql = "INSERT INTO good(goodname,day,month,year,username) VALUES(?,?,?,?,?)";
         PreparedStatement prep = conn.prepareStatement(sql);
         prep.setString(1, good.getGoodname());
         prep.setInt(2, 18);
         prep.setInt(3,7);
         prep.setInt(4, 2022);
         prep.setString(5, "苟宸雨");

         //3. 结果
         int row = prep.executeUpdate();

         //4. 关闭连接
         conn.close();

         return row;
    }


    //删除
    public int delete(int id) throws SQLException {
        //1. 连接
        Connection conn = JDBCUtil.getConnection();

        //2. sql
        String sql = "delete from good where id=?";
        PreparedStatement prep = conn.prepareStatement(sql);
        prep.setInt(1, id);

        //3. 结果
        int row = prep.executeUpdate();

        //4. 关闭连接
        conn.close();

        return row;
    }

    //查找物品
    public List<Good> findGood(String Goodname) throws SQLException
    {
        //1. 连接
        Connection conn = JDBCUtil.getConnection();
        //2. sql
        String sql = "SELECT * from good WHERE goodname=?;";
        PreparedStatement prep = conn.prepareStatement(sql);
        prep.setString(1, Goodname);
        //3. 结果
        ResultSet rs = prep.executeQuery();
        //打印查找到的物品信息
        List<Good> list=new ArrayList<>();
        while (rs.next())
        {
            Good good=new Good();



            int id=rs.getInt("id");
            String goodname=rs.getString("goodname");
            int year=rs.getInt("year");
            int month=rs.getInt("month");
            int day=rs.getInt("day");
            good.setId(id);
            good.setGoodname(goodname);
            good.setYear(year);
            good.setMonth(month);
            good.setDay(day);

            list.add(good);
        }
        //4. 关闭连接
        conn.close();
        return list;
    }

    public static void main(String[] args) throws SQLException {
//   /addServlet?id=1&goodname=cat&year=2022&month=6&day=1&username=Tom

    }

}

